﻿using System.Collections.Generic;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using WF_DataAccess;
using Common;
using WF_Business;

namespace Business.FlowOperation
{
    /// <summary>
    /// 工作创建、流转操作类
    /// </summary>
    public class RequestFlowOperation
    {
        /// <summary>
        /// 根据用户id获取所能受理的工作流
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <returns></returns>
        public DataTable GetWorkFlowByUserId(string strUserId)
        {
            string strSql = string.Format(@"select m.* from st_workflow m, st_step n  Where  m.WID in 
                (select WID from st_group_in_step where GID in (select GID from st_user_group t 
                where t.userid = '{0}') And step_type=0 ) and m.WID = n.Wid and n.step_type = 0 and m.rot = 0 order by m.wname",
            strUserId);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 创建流程实例
        /// </summary>
        /// <param name="strWid"></param>
        /// <param name="strUserId"></param>
        /// <param name="strRequestName"></param>
        /// <param name="strParamsWiid"></param>
        /// <returns></returns>
        public int BuildTask(string strWid, string strUserId, string strRequestName, ref string strParamsWiid, ref string strErrorMsg)
        {
            WorkFlow work = new WorkFlow();
            string strIid = "", strWiid = "";
            int nReturn = 0;
            IDataAccess dataAccess = SysParams.OAConnection(true);

            try
            {
                if (true == work.NewInstance(strWid, strUserId, strRequestName, ref strIid, ref strWiid, ref strErrorMsg, ref dataAccess))
                {
                    //插入工作流实例的扩展信息
                    string strSql = string.Format("insert into xt_instance_ext(iid,申请单位) values('{0}','{1}')",
                        strIid, strRequestName);
                    dataAccess.RunSql(strSql);

                    strParamsWiid = strWiid;
                    dataAccess.Close(true);
                    nReturn = 1;
                }
            }
            catch
            {
                nReturn = -1;
                dataAccess.Close(false);
            }
            return nReturn;
        }

        /// <summary>
        /// 显示待办案件列表
        /// </summary>
        /// <param name="strUserId"></param>
        /// <param name="searchWord"></param>
        /// <returns></returns>
        public DataTable AppearWorkItem(string strUserId, string searchWord)
        {
            string strSql = string.Format(@"select 顺序号,
               案件类型,
               申请单位,
               接收时间,
               前一用户,
               剩余工作日,
               所在岗位,
               优先级,
               案件状态,
               userid,
               督办过期,
               结点过期,
               wiid,
               isback,stepctlid,
               extotaltime,
               联系人
          from sv_work_list_for_worklist_node a
         where (active = 1 or active = 0)
           and (userid = '{0}' or
               userid in (select userid
                             from st_delegate
                            Where delegate_userid = '{0}'
                              And (delegate_task = a.案件类型 or delegate_task = '全部')
                              and begin_time <= Sysdate
                              And end_time >= Sysdate))
           and 顺序号 not in (select iid from st_suspend where end_date is null) {1} 
           and 顺序号 not in
               (select serial from xt_suspend_apply where checkuserid is null)
         order by 接收时间 desc",
               strUserId, searchWord);

            DataTable dtReturn = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            
            return dtReturn;
        }

        /// <summary>
        /// 获取待办件并返回json数据
        /// </summary>
        /// <param name="strUserId"></param>
        /// <param name="searchWord"></param>
        /// <returns></returns>
        public string AppearWorkItemWithJson(string strUserId, string searchWord)
        {
            string strSql = string.Format(@"select 顺序号,
               案件类型,
               申请单位,
               接收时间,
               前一用户,
               剩余工作日,
               所在岗位,
               优先级,
               案件状态,
               userid,wid,
               督办过期,
               结点过期,
               wiid,
               isback,stepctlid,
               extotaltime,
               联系人
          from sv_work_list_for_worklist_node a
         where (active = 1 or active = 0)
           and (userid = '{0}' or
               userid in (select userid
                             from st_delegate
                            Where delegate_userid = '{0}'
                              And (delegate_task = a.案件类型 or delegate_task = '全部')
                              and begin_time <= Sysdate
                              And end_time >= Sysdate))
           and 顺序号 not in (select iid from st_suspend where end_date is null) {1} 
           and 顺序号 not in
               (select serial from xt_suspend_apply where checkuserid is null)
         order by 接收时间 desc",
               strUserId, searchWord);

            DataTable dtReturn = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            JsonOperation joTemp = new JsonOperation();
            return joTemp.BindJson(dtReturn);
        }

        /// <summary>
        /// 获取流程的资源
        /// </summary>
        /// <param name="strWname"></param>
        /// <returns></returns>
        public string GetStaticResouceWithJson(string strWid)
        {
            string strSql = string.Format(@"select *
                  from st_static_resource a
                 where wid = '{0}'
                   and type = '3'", strWid);

            DataTable dtReturn = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            JsonOperation joTemp = new JsonOperation();
            return joTemp.BindJson(dtReturn);
        }

        /// <summary>
        /// 岗位名称
        /// </summary>
        /// <param name="strWiid"></param>
        /// <returns></returns>
        public string GetStep(string strWiid)
        {
            string strSql = string.Format("select step from st_work_item where wiid='{0}'", strWiid);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取岗位信息
        /// </summary>
        /// <param name="strWiid"></param>
        /// <returns></returns>
        public DataTable GetStepDetail(string strWiid)
        {
            string strSql = string.Format("select stepgroup,stepgroupctlid,stepctlid from st_work_item where wiid='{0}'", strWiid);
            DataTable dtReturn = new DataTable();
            SysParams.OAConnection().RunSql(strSql,out dtReturn);
            return dtReturn;
        }



        /// <summary>
        /// 获取stepGroup
        /// </summary>
        /// <param name="strWiid"></param>
        /// <returns></returns>
        public string GetStepGroup(string strWiid)
        {
            string strSql = string.Format("select stepgroup from st_work_item where wiid='{0}'", strWiid);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 判断是否有终止的权限
        /// </summary>
        /// <param name="strWiid"></param>
        /// <returns></returns>
        public string IsHaveStopRight(string strStepName)
        {
            string strSql = string.Format("select IS_STOP from ST_GROUP where GROUP_NAME='{0}'", strStepName);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取是否有回退信息
        /// </summary>
        /// <param name="striid"></param>
        /// <returns></returns>
        public string GetBackInfo(string striid)
        {
            string strSql = string.Format(@"SELECT SNAME FROM ST_FLOW WHERE Wid in(SELECT Wid FROM 
                ST_INSTANCE WHERE IID='{0}' and isdelete <>1) AND Is_Back=1", striid);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取最大iid（判断是否能回退）
        /// </summary>
        /// <param name="strIid"></param>
        /// <param name="strUserId"></param>
        /// <returns></returns>
        public string HaveFlowRecord(string strIid, string strUserId)
        {
            string strSql = string.Format(@"Select max(wiid) From st_work_item_hist Where wiid In 
                (select prev_wiid from st_work_item_stack t Where IID='{0}') ",strIid);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取表单数量
        /// </summary>
        /// <param name="wid"></param>
        /// <param name="step"></param>
        /// <param name="path"></param>
        /// <param name="formid"></param>
        /// <returns></returns>
        public string GetFormTableNum(string wid, string step, string path, string formid)
        {
            string strSql = string.Format(@"select Count(wid) from st_workflow_form_right Where wid='{0}'  And step='{1}'
                                            And (form_path='{2}'  OR FormId='{3}' ) And input_right>1", wid, step, path, formid);

            return SysParams.OAConnection().GetValue(strSql);

        }

        /// <summary>
        /// 获取文件扩展名
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        public string GetAttachementExtName(string aid)
        {
            string strSql = string.Format(@"select ext_name from st_attachment where aid='{0}'", aid);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取wid
        /// </summary>
        /// <param name="whereValue"></param>
        /// <returns></returns>
        public string GetWidFromStinstance(string whereValue)
        {
            string strSql = string.Format(@"select WID from st_instance Where 1=1 {0} ", whereValue);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 设置交换位
        /// </summary>
        /// <param name="flag"></param>
        /// <param name="iid"></param>
        public void UpdateChangeFlag(string flag, string iid)
        {
            string strSql = string.Format(@"update xt_instance_ext set 交换否='{0}' where iid='{1}'", flag, iid);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 设置交换位
        /// </summary>
        /// <param name="flag"></param>
        /// <param name="iid"></param>
        public void UpdateChangeFlag(string flag,string remark, string iid)
        {
            string strSql = string.Format(@"update xt_instance_ext set 交换否='{0}' ,stop_remark ='{1}' where iid='{2}'", flag, remark, iid);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 设置归档标志位
        /// </summary>
        /// <param name="flag"></param>
        /// <param name="iid"></param>
        public void UpdateArchiveFlag(string flag, string iid)
        {
            string strSql = string.Format(@"update xt_submit_info set SUBMITFLAG='{0}' where iid=(select 关联ID from xt_instance_ext where iid='{1}')", flag, iid);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取流程名称
        /// </summary>
        /// <param name="iid"></param>
        /// <returns></returns>
        public string GetWnameFromStinsance(string iid)
        {
            string strSql = string.Format("Select WNAME From ST_WORKFLOW Where WID IN(select WID from st_instance Where iid='{0}' and isdelete <>1)", iid);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取消息列表
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="wid"></param>
        /// <returns></returns>
        public DataTable GetMsgList(string iid, string wid)
        {
            string strSql = string.Format(@"select u.userid,u.mobile,u.mobilebak,u.user_name from st_user u where u.userid in( select w.userid from st_work_item w 
                                            where w.step in (select f.ename from st_flow f where 1=1  and f.wid='{0}') and iid={1} )", wid, iid);
            DataTable dtReturn = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 获取操作权限
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable GetRightList(string userid)
        {
            string strSql = string.Format(@"select u.userid,u.mobile,u.mobilebak,u.user_name from st_user u where u.userid in (select delegate_userid
                                            from st_delegate p where p.userid ='{0}')", userid);
            DataTable dtReturn = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 根据wiid获取iid
        /// </summary>
        /// <param name="wiid"></param>
        /// <returns></returns>
        public string GetIidFromWiid(string wiid)
        {
            string strSql = string.Format(@"select iid from st_work_item where wiid='{0}'", wiid);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 更新案件信息标志位
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="status"></param>
        public void UpdateStatusFlag(string iid, string status)
        {
            string strSql = string.Format("update st_instance t set t.status='{1}' where t.iid='{0}' and isdelete <>1", iid,status);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 发送短信息
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="messagetext"></param>
        public void SetMessage(string iid,string messagetext)
        {
            string strSql = string.Format(@"Insert into xt_messagebox(MESSAGETEXT, PHONENO, USERNAME, USERID)
                                            select username|| ',您好，{1}',a.mobile,username,a.userid
                                            from sys_user a
                                            where userid =(select REQUESTER from XT_SUBMIT_INFO
                                            where iid = (select 关联ID from xt_instance_ext where iid = '{0}'))", iid, messagetext);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 更新自定义工作流
        /// </summary>
        /// <param name="txtInterfaceUrl"></param>
        /// <param name="nFaceType"></param>
        /// <param name="ddlWorkflowFace"></param>
        public static void UpdateFlow(string txtInterfaceUrl, int nFaceType, string ddlWorkflowFace)
        {
            string strSql = string.Format("update xt_workflow_define set INTERFACEURL='{0}',INTERFACETYPE='{1}' where id='{2}'",
            txtInterfaceUrl, nFaceType, ddlWorkflowFace);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取所有流程名称
        /// </summary>
        /// <param name="strParamsName">流程名称</param>
        /// <returns></returns>
        public static DataTable GetWorkFlow(string strParamsName)
        {
            string strSql = string.Empty;
            strSql = "select flowname keyvalue,id keycode from xt_workflow_define where ispub='1' and isdelete='0'";
            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 获取非一级部门列表
        /// </summary>

        /// <returns></returns>
        /// <!--addby zhongjian 20091117-->
        public static DataTable GetDepList()
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select * from st_department t where departid<>35 
                 start with t.parent_id=0
                 connect by prior departid=parent_id");
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 判断批量提交件信息中是否存在不同流程名和岗位名
        /// </summary>
        /// <param name="strWIID">业务环节编号</param>
        /// <returns></returns>
        public string IsHaveSameSerialNameAndStep(string strWIID)
        {
            string strSql = string.Format("select count(distinct wid) from sv_work_list_for_worklist_node where wiid in ({0})", strWIID);
            string strCount = SysParams.OAConnection().GetValue(strSql);

            string strReturn = "";
            if (strCount != "1")
            {
                strReturn = "该批次中存在不同的流程，系统不允许提交";
            }
            else
            {
                strSql = string.Format("select count(distinct 所在岗位) from sv_work_list_for_worklist_node where wiid in ({0})", strWIID);
                strCount = SysParams.OAConnection().GetValue(strSql);

                if (strCount != "1")
                {
                    strReturn = "该批次中存在统一流程的不同岗位，系统不允许提交";
                }
            }

            return strReturn;
        }

    }
}
